#! /usr/bin/env python
# _*_ coding:UTF-8 _*_
"""
@author:zhh
@Software:PyCharm
@blogs:https://blog.csdn.net/smilehaoh
@time:2020/3/31

"""
from common.connect_db import MyDB
import operator

my_db = MyDB()


class Coin:
    @staticmethod
    def get_old_total_coin():
        """
        查询老系统会员的总微币
        :return:
        """
        sql1 = "SELECT sum(credit2) as oldtotalcoin from ims_ewei_shop_member where (mobile regexp '[^0-9.]')=0"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_total_coin1():
        """
        查询老系统会员的总微币
        :return:
        """
        sql1 = "SELECT credit2 as oldtotalcoin from ims_ewei_shop_member where  (mobile regexp '[^0-9.]')=0"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_total0_coin():
        """
        查询老系统会员的总微币为0的情况
        :return:
        """
        sql1 = "SELECT count(credit2) as oldtotalcoin from ims_ewei_shop_member where credit2=0 and (mobile regexp '[^0-9.]')=0"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_coin(normal_level):
        """
        查询老系统其他会员的总微币
        :return:
        """
        sql = "SELECT sum(credit2) as oldnormalcoin from ims_ewei_shop_member where (mobile regexp '[^0-9.]')=0 and level='%s'" % normal_level
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_coin(levelnumber):
        """
        查询新系统普通会员总微币
        :return:
        """
        sql = "select sum(now_coin) as totalcoin  from common_coin_coin where member_id in (select member_id from common_coin_credits where level='%s' and account_type=1) and account_type=1" % levelnumber
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_vipcoin():
        """
        查询新系统vip会员总微币
        :return:
        """
        sql = "select sum(now_coin) as totalcoin  from common_coin_coin where member_id in (select member_id from common_coin_credits where level in (20,30,40)) and account_type=1"
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_total_coin():
        """
        查询新系统会员的总微币
        :return:
        """
        sql = "select sum(now_coin) as number_now_coin  from common_coin_coin where account_type=1 "
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_total_coin1():
        """
        查询新系统会员的总微币
        :return:
        """
        sql = "select now_coin as number_now_coin  from common_coin_coin where account_type=1 "
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_total0_coin():
        """
        查询新系统会员的总微币为0的数量
        :return:
        """
        sql = "select count(*) as number_now_coin  from common_coin_coin where now_coin=0 and account_type=1 "
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def new_coincompare():
        """
        :return:
        """
        sql1 = "select now_coin as coincompare  from common_coin_coin where member_id in (select member_id from common_coin_credits where level=10 and account_type=1) and account_type=1"
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def old_coincompare():
        """
        :return:
        """
        sql = "SELECT credit2 as oldnormalcoin from ims_ewei_shop_member" \
              " where level=1 and (mobile regexp '[^0-9.]')=0"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_earnmoney():
        """
        查询老系统会员总共赚了多少钱
        :return:
        """
        # sql1="select (t.credit2+sum(l.money)) as earnMoney,t.id from ims_ewei_shop_member t join  ims_ewei_shop_member_log l on l.mid=t.id where l.type=1 and l.status=2  and (t.mobile regexp '[^0-9.]') = 0 group by t.id order by earnMoney desc limit 10"
        sql1 = "select sum(money) as earnMoney from ims_ewei_shop_member_log where type=1 and `status`=2"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_credit2():
        sql1 = "select sum(credit2) as totalcredit2 from ims_ewei_shop_member where mobile regexp '[^0-9.]' = 0;"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_earnmoney():
        """
        :return:
        """
        sql1 = "select sum(total_coin) as earnMoney from  common_coin_coin order by earnMoney desc"
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def old():
        """
        :return:
        """
        sql1 = "select id,mobile,level  from  ims_ewei_shop_member where id in (2,3,4,5) order by id desc"
        result = my_db.execute_select(select_db='old', db_name="quan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def new():
        """
        :return:
        """
        sql1 = "select credits_id as id from life_member where id in (2,3,4,5) order by id desc"
        result = my_db.execute_select(select_db='new', db_name='coin', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")


if __name__ == '__main__':
    coin = Coin()
    newearn = coin.get_new_earnmoney()
    newearn = newearn[0][0]
    print(newearn)
    oldearn = coin.get_old_earnmoney()
    oldearn = oldearn[0][0]
    print(oldearn)
    oldcredit2 = coin.get_old_credit2()
    oldcredit2 = oldcredit2[0][0]
    print(oldcredit2)
